create database Text
go
use Text
go
--1. ������10�֣�
--������±�Author�ṹ����������д������������SQL��䣺

create TABLE Author 
(
   Id  int  NOT NULL PRIMARY KEY identity,
   FirstName  nvarchar(45) NOT NULL,
   LastName  nvarchar(45) NOT NULL,
   UpdatedTime  datetime NOT NULL
 )

--��FirstName����Ψһ����uniq_idx_firstname����LastName������ͨ����idx_lastname

--FirstName��Ψһ����
create unique index uniq_idx_firstname
on Author(FirstName asc)
--LastName����ͨ����
create index idx_lastname
on Author(LastName asc)





--2. ������15�֣�
--����һ��������trg_AuditLog������Employees���в���һ�����ݵ�ʱ�򣬴���������ص����ݵ�AuditLog�С�

-- ְԱ��
CREATE TABLE Employees
(
   Id INT PRIMARY KEY NOT NULL identity,
   Name nvarchar(80) NOT NULL,
   Age INT NOT NULL,
   Address NVARCHAR(50),
   SALARY decimal(18,2)
);
-- �����־��
CREATE TABLE AuditLog
(
   Id int primary key not null identity,
   NAME TEXT NOT NULL,
   Salary decimal(18,2)
);

--��̨����Employees����һ������:
--INSERT INTO Employees (NAME,AGE,ADDRESS,SALARY)VALUES ('Paul', 32, 'California', 20000.00 );
--Ȼ���AuditLog����ʹ�ò�ѯ���:
--select * from AuditLog;

--�����
--Paul | 20000.00
go
create trigger trg_AuditLog
on
Employees
after
insert
as
begin
	insert into employees (name,age,address,salary)
	values ('paul', 32, 'california', 20000.00 )
	select * from AuditLog
end




--3. ������10�֣�
--���Author��������ͼvw_Author��ֻ����FirstName�Լ�LastName���У���������������������FirstNameΪv_FirstName��LastName�޸�Ϊv_LastName��
--CREATE TABLE Author 
--(
--   Id  int  NOT NULL PRIMARY KEY identity,
--   FirstName  nvarchar(45) NOT NULL,
--   LastName  nvarchar(45) NOT NULL,
--   UpdatedTime  datetime NOT NULL
--)
--��̨�����2������:
--insert into Author (FirstName,LastName,UpdatedTime) values ('PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('NICK', 'WAHLBERG', '2006-02-15 12:34:33');

--��ѯ��ͼ��select * from vw_Author;
--�����
--['first_name_v', 'last_name_v']
--PENELOPE|GUINESS
--NICK|WAHLBERG
go
create view vw_Author 
as
select FirstName v_FirstName,LastName v_LastName
from Author
go
insert into Author (FirstName,LastName,UpdatedTime) values ('PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), ('NICK', 'WAHLBERG', '2006-02-15 12:34:33');
select * from vw_Author




--4. ������10�֣�
--��ҳ��ѯEmployees����ÿ5��һҳ�����ص�2ҳ������
drop table Employees
go
CREATE TABLE Employees
(
   Id int primary key NOT NULL identity,
   EmployeeCode nvarchar(80),
   Birthday date NOT NULL,
   FirstName nvarchar(14) NOT NULL,
   LastName nvarchar(16) NOT NULL,
   Gender char(1) NOT NULL,
   HireDate date NOT NULL
);
--����һЩ���ݣ�
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10001','1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10002','1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10003','1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10004','1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10005','1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10006','1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10007','1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10008','1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10009','1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10010','1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees (EmployeeCode,Birthday,FirstName,LastName,Gender,HireDate)  VALUES('10011','1953-11-07','Mary','Sluis','F','1990-01-22');

--��ѯ��5��ÿҳ����2ҳ�ļ�¼�������
--6|10006|1953-04-20|Anneke|Preusig|F|1989-06-02
--7|10007|1957-05-23|Tzvetan|Zielinski|F|1989-02-10
--8|10008|1958-02-19|Saniya|Kalloufi|M|1994-09-15
--9|10009|1952-04-19|Sumant|Peac|F|1985-02-18
--10|10010|1963-06-01|Duangkaew|Piveteau|F|1989-08-24
select * from Employees
order by Id asc
offset (2-1)*5 rows fetch next 5 rows only
